# -*- coding:utf-8 -*-


import pymysql
from spider.common.util.StringUtil import StrUtil


class MySQLUtils:
    def __init__(self, host='localhost', port=3306, user='root', password='178121', database='db2024'):
        self.converter = StrUtil()
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
        self.connection = pymysql.connect(host=self.host, user=self.user,port=self.port ,password=self.password,db=self.database,charset='utf8')

    def connect(self):
        return self.connection

    def disconnect(self):
        if self.connection is not None:
            self.connection.close()
            print("Disconnected from MySQL database")

    def execute_query(self, query):
        result = None
        try:
            with self.connection.cursor() as cursor:
                cursor.execute(query)
                result = cursor.fetchall()
        except pymysql.Error as e:
            print(f"Error executing query: {e}")
        return result

    def execute_query2(self, query):
        result = None
        try:
            with self.connection.cursor() as cursor:
                cursor.execute(query)
                columns = [column[0] for column in cursor.description]
                result = [dict(zip(columns, row)) for row in cursor.fetchall()]
        except pymysql.Error as e:
            print(f"Error executing query: {e}")
        return result

    def execute_update(self, query,raise_flag=False):
        try:
            with self.connection.cursor() as cursor:
                cursor.execute(query)
            self.connection.commit()
        except pymysql.Error as e:
            if raise_flag:
                raise e
            print(f"Error executing update: {e}")

    def create(self, table, data):
        keys = list(data.keys())
        keys = list(map(self.converter.camel_to_snake, keys))
        columns = ', '.join(keys)
        values = ', '.join([f"'{self.toStr(value)}'" for value in data.values()])
        query = f"INSERT INTO {table} ({columns}) VALUES ({values})"
        self.execute_update(query)

    def create_with_check(self, table, data,show_sql=False):
        columns = self.retrieve_columns(table)
        keys = list(data.keys())
        keys = list(map(self.converter.camel_to_snake, keys))
        keys=[i for i in keys if i in columns]
        values=[self.toStr(data[key]) for key in data.keys() if self.converter.camel_to_snake(key) in keys]

        keysStr = ', '.join(keys)
        valuesStr = ', '.join([f"'{self.toStr(value)}'" for value in values])

        query = f"INSERT INTO {table} ({keysStr}) VALUES ({valuesStr})"
        if show_sql:
            print(query)
        self.execute_update(query)

    def batch_create_with_check(self, table, datas, show_sql=False):
        columns = self.retrieve_columns(table)
        keys = list(datas[0].keys())
        keys = list(map(self.converter.camel_to_snake, keys))
        keys = [i for i in keys if i in columns]


        values_es = [[ data.get(key) if data.get(key)!=None else data.get(self.converter.snake_to_camel(key)) for key in keys] for data in datas]
        value_joins=[]
        for values in values_es:
            valuesStr = ', '.join([f"'{self.toStr(value)}'" for value in values])
            value_joins.append('({})'.format(valuesStr))
        keysStr = ', '.join(keys)
        valuesStr=',\n'.join(value_joins)
        query = f"INSERT INTO {table} ({keysStr}) VALUES {valuesStr}"
        if show_sql:
            print(query)
        self.execute_update(query)




    def update(self, table, data, condition,show_sql=False,raise_flag=False):
        updates = ', '.join([f"{key}='{value}'" for key, value in data.items()])
        query = f"UPDATE {table} SET {updates} WHERE {condition}"
        if show_sql:
            print(f'更新语句:{query}')
        self.execute_update(query,raise_flag=raise_flag)


    def delete(self, table, condition,show_sql=False):
        query = f"DELETE FROM {table} "
        if condition:
            query += f" WHERE {condition}"
        if show_sql:
            print(query)
        self.execute_update(query)

    def retrieve(self, table, condition='',show_sql=False):
        query = f"SELECT * FROM {table}"
        if condition:
            query += f" WHERE {condition}"
        if show_sql:
            print(query)
        return self.execute_query(query)

    def retrieve_jsons(self, table, condition='',show_sql=False):
        query = f"SELECT * FROM {table}"
        if condition:
            query += f" WHERE {condition}"
        if show_sql:
            print(query)
        jsons = self.execute_query2(query)
        jsons=[{self.converter.snake_to_camel(key): value for key, value in json.items()}  for json in jsons]
        return jsons

    def retrieve_json(self, table, condition='',show_sql=False):
       jsons = self.retrieve_jsons(table=table, condition=condition,show_sql=show_sql)
       if jsons==None or len(jsons)==0:
           return None

       return jsons[0]

    def retrieve_count(self, table, condition='',show_sql=False):
        query = f"SELECT count(1) FROM {table}"
        if condition:
            query += f" WHERE {condition}"
        if show_sql:
            print(query)
        try:
            return self.execute_query(query)[0][0]
        except:
            return 0

    def retrieve_columns(self, table):
        query = f"SHOW COLUMNS FROM {table}"
        execute_query = self.execute_query(query)
        result = [column[0] for column in execute_query]
        return result

    def retrieve_sql(self, sql):
        execute_query = self.execute_query(sql)
        return execute_query

    def toStr(self, data):
        if isinstance(data, list):
            return '@@'.join(data)
        if data==None:
            return ''
        return data


